﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.Common;
using Vit.Database.DataAccess;
using Vit.Database.Schema;

namespace Vit.Database.Sql
{
    public class SqlDatabaseWrapper : DatabaseWrapper
    {
        public SqlDatabaseWrapper(string connectionString)
            : base(connectionString)
        {
        }

        public SqlDatabaseWrapper(string connectionString, IDataInstrumentationProvider dataInstrumentationProvider)
            : base(connectionString, dataInstrumentationProvider)
        {
        }

        public override void InitialDatabase()
        {
            db = new SqlDatabase(connectionString, dataInstrumentationProvider); 
        }

        public override void RefreshDatabaseSchemas()
        {
            dbSchema = new DatabaseSchema(this);

            SprocAccessor<ColumnSchemaInfo> daColInfo = new SprocAccessor<ColumnSchemaInfo>(db, this.SP_GetColumnObjectInfo, new ColumnSchemaInfoRowMapper());
            IEnumerable<ColumnSchemaInfo> cols = daColInfo.Execute();

            SprocAccessor<ParameterSchemaInfo> daParaInfo = new SprocAccessor<ParameterSchemaInfo>(db, this.SP_GetParameterObjectInfo, new ParameterSchemaInfoRowMapper());
            IEnumerable<ParameterSchemaInfo> paras = daParaInfo.Execute();

            dbSchema.Refresh(cols, paras);
        }

        public override void RefreshDatabaseInfo()
        {
        }

        public override void KillThreads()
        {
            throw new NotImplementedException();
        }

        public override void ViewBlockThreads()
        {
            throw new NotImplementedException();
        }

        #region utils

        public override void CreateKillThreadsUtility()
        {
            throw new NotImplementedException();
        }

        public override void CreateViewBlockThreadsUtility()
        {
            throw new NotImplementedException();
        }

        public override void CreateSchemaInfoUtility()
        {
            db.ExecuteNonQuery(CommandType.Text, this.SQL_GetColumnObjectInfo);
            db.ExecuteNonQuery(CommandType.Text, this.SQL_GetParameterObjectInfo);
        }

        public override void CreateDatabaseInfoUtility()
        {
            throw new NotImplementedException();
        }

        #endregion

        #region convert types and build names

        public override DbType ToDbType(string nativeType)
        {
            DbType dataType = DbType.String;
            #region SqlServer
            switch (nativeType.Trim().ToLower())
            {
                case "bigint":
                    dataType = DbType.Int64;
                    break;
                case "binary":
                    dataType = DbType.Binary;
                    break;
                case "bit":
                    dataType = DbType.Boolean;
                    break;
                case "char":
                    dataType = DbType.StringFixedLength;
                    break;
                case "date":
                    dataType = DbType.Date;
                    break;
                case "datetime":
                    dataType = DbType.DateTime;
                    break;
                case "datetime2":
                    dataType = DbType.DateTime2;
                    break;
                case "datetimeoffset":
                    dataType = DbType.DateTimeOffset;
                    break;
                case "decimal":
                    dataType = DbType.Decimal;
                    break;
                case "float":
                    dataType = DbType.Double;
                    break;
                case "image":
                    dataType = DbType.Binary;
                    break;
                case "int":
                    dataType = DbType.Int32;
                    break;
                case "money":
                    dataType = DbType.Currency;
                    break;
                case "nchar":
                    dataType = DbType.StringFixedLength;
                    break;
                case "ntext":
                    dataType = DbType.String;
                    break;
                case "numeric":
                    dataType = DbType.VarNumeric;
                    break;
                case "nvarchar":
                    dataType = DbType.String;
                    break;
                case "real":
                    dataType = DbType.Double;
                    break; ;
                case "smalldatetime":
                    dataType = DbType.Date;
                    break;
                case "smallint":
                    dataType = DbType.Int16;
                    break;
                case "smallmoney":
                    dataType = DbType.Currency;
                    break;
                case "sql_variant":
                    dataType = DbType.Object;
                    break; ;
                case "text":
                    dataType = DbType.String;
                    break;
                case "time":
                    dataType = DbType.Time;
                    break;
                case "tinyint":
                    dataType = DbType.Int16;
                    break; ;
                case "uniqueidentifier":
                    dataType = DbType.Guid;
                    break; ;
                case "varbinary":
                    dataType = DbType.Binary;
                    break; ;
                case "varchar":
                    dataType = DbType.String;
                    break; ;
                case "xml":
                    dataType = DbType.Xml;
                    break; ;
                default:
                    dataType = DbType.String;
                    break;
            }

            #endregion
            return dataType;
        }

        public override Type ToSystemType(string nativeType, bool nullable)
        {
            Type dataType = null;
            #region SqlServer
            switch (nativeType.Trim().ToLower())
            {
                case "bigint":
                    dataType = nullable ? typeof(long?) : typeof(long);
                    break;
                case "binary":
                    dataType = typeof(System.Data.Linq.Binary);
                    break;
                case "bit":
                    dataType = nullable ? typeof(bool?) : typeof(bool);
                    break;
                case "char":
                    dataType = typeof(string);
                    break;
                case "date":
                case "datetime":
                case "datetime2":
                    dataType = nullable ? typeof(DateTime?) : typeof(DateTime);
                    break;
                case "datetimeoffset":
                    dataType = nullable ? typeof(DateTimeOffset?) : typeof(DateTimeOffset);
                    break;
                case "decimal":
                    dataType = nullable ? typeof(decimal?) : typeof(decimal);
                    break;
                case "float":
                    dataType = nullable ? typeof(double?) : typeof(double);
                    break;
                case "image":
                    dataType = typeof(System.Data.Linq.Binary);
                    break;
                case "int":
                    dataType = nullable ? typeof(int?) : typeof(int);
                    break;
                case "money":
                    dataType = nullable ? typeof(decimal?) : typeof(decimal);
                    break;
                case "nchar":
                    dataType = typeof(string);
                    break;
                case "ntext":
                    dataType = typeof(string);
                    break; ;
                case "numeric":
                    dataType = nullable ? typeof(decimal?) : typeof(decimal);
                    break; ;
                case "nvarchar":
                    dataType = typeof(string);
                    break; ;
                case "real":
                    dataType = nullable ? typeof(float?) : typeof(float);
                    break; ;
                case "smalldatetime":
                    dataType = nullable ? typeof(DateTime?) : typeof(DateTime);
                    break; ;
                case "smallint":
                    dataType = nullable ? typeof(short?) : typeof(short);
                    break; ;
                case "smallmoney":
                    dataType = nullable ? typeof(decimal?) : typeof(decimal);
                    break; ;
                case "sql_variant":
                    dataType = typeof(object);
                    break; ;
                case "text":
                    dataType = typeof(string);
                    break;
                case "time":
                    dataType = nullable ? typeof(TimeSpan?) : typeof(TimeSpan);
                    break; ;
                case "timestamp":
                    dataType = typeof(System.Data.Linq.Binary);
                    break; ;
                case "tinyint":
                    dataType = nullable ? typeof(byte?) : typeof(byte);
                    break;
                case "uniqueidentifier":
                    dataType = nullable ? typeof(Guid?) : typeof(Guid);
                    break; ;
                case "varbinary":
                    dataType = typeof(System.Data.Linq.Binary);
                    break; ;
                case "varchar":
                    dataType = typeof(string);
                    break;
                case "xml":
                    dataType = typeof(System.Xml.Linq.XElement);
                    break;
                default:
                    dataType = typeof(string);
                    break;
            }

            #endregion
            return dataType;
        }

        public override object ConvertValueType(string nativeType, object value)
        {
            if (value == null || string.IsNullOrEmpty(value.ToString()))
                return DBNull.Value;
            switch (nativeType.Trim().ToLower())
            {
                case "uniqueidentifier":
                    return new Guid(value.ToString());
                case "xml":
                    return System.Xml.Linq.XElement.Parse(value.ToString());
            }
            return value;
        }

        public override string BuildFullName(string shcema, string owner, string name)
        {
            return string.Format("[{0}].[{1}]", shcema, name);
        }

        public override string BuildSafeName(string name)
        {
            return string.Format("[{0}]", name);
        }

        public override string BuildParameterName(string name)
        {
            return string.Format("@{0}", name);
        }

        public override string BuildOriginalParameterName(string name)
        {
            return string.Format("@Original_{0}", name);
        }

        #endregion

        #region utils sql strings

        #region schema sqls

        private string SP_GetColumnObjectInfo
        {
            get { return "sp_util_GetColumnObjectInfo"; }
        }

        private string SQL_GetColumnObjectInfo
        {
            get
            {
                return string.Format(@"
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[{0}]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[{0}]
GO

create procedure [dbo].[{0}]
as
begin
SELECT TC.CONSTRAINT_SCHEMA AS SCHEMA_NAME
,TC.CONSTRAINT_NAME
,TC.TABLE_NAME,TC.CONSTRAINT_TYPE
,FKCU.COLUMN_NAME,FKCU.ORDINAL_POSITION
,PKCU.CONSTRAINT_SCHEMA AS REF_SCHEMA_NAME,PKCU.TABLE_NAME AS REF_TABLE_NAME,PKCU.COLUMN_NAME AS REF_COLUMN_NAME
INTO #TEMP_COLUMN_INFO_VIEW	
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS TC
	LEFT JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE FKCU
	ON FKCU.CONSTRAINT_NAME=TC.CONSTRAINT_NAME
	LEFT JOIN INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS RC
	ON RC.CONSTRAINT_NAME=FKCU.CONSTRAINT_NAME
	LEFT JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE PKCU
	ON PKCU.CONSTRAINT_NAME=RC.UNIQUE_CONSTRAINT_NAME
	AND PKCU.ORDINAL_POSITION=FKCU.ORDINAL_POSITION
ORDER BY TC.TABLE_NAME,TC.CONSTRAINT_TYPE DESC,FKCU.ORDINAL_POSITION;

SELECT COL.TABLE_SCHEMA AS SchemaName
,SCH.SCHEMA_OWNER AS OwnerName
,CASE WHEN TBL.TABLE_TYPE='BASE TABLE' THEN 1 ELSE 2 END AS ObjectType
,COL.TABLE_NAME AS ObjectName
,(select top 1 value from ::fn_listextendedproperty(NULL, 'user', SCH.SCHEMA_OWNER, 'table', COL.TABLE_NAME, null, null) where name='MS_Description') as ObjectDescription
,COL.COLUMN_NAME AS ColumnName
,COL.ORDINAL_POSITION AS ColumnOrder
,COL.DATA_TYPE AS NativeType
,CASE WHEN COL.IS_NULLABLE='NO' THEN 0 ELSE 1 END AS AllowDBNull
,COL.CHARACTER_MAXIMUM_LENGTH AS Size
,COL.NUMERIC_PRECISION AS [Precision],COL.NUMERIC_SCALE AS Scale
,CASE WHEN COLUMNPROPERTY(OBJECT_ID(COL.TABLE_NAME), COL.COLUMN_NAME,'IsIdentity')='1' THEN 1 ELSE 0 END AS IsIdentity
,(select top 1 value from ::fn_listextendedproperty(NULL, 'user', SCH.SCHEMA_OWNER, 'table', COL.TABLE_NAME, 'column', COL.COLUMN_NAME) where name='MS_Description') as ColumnDescription
,PrimaryKeyName=(
	SELECT CONSTRAINT_NAME
	FROM #TEMP_COLUMN_INFO_VIEW
	WHERE CONSTRAINT_TYPE='PRIMARY KEY'
	AND SCHEMA_NAME=COL.TABLE_SCHEMA
	AND TABLE_NAME=COL.TABLE_NAME
	AND COLUMN_NAME=COL.COLUMN_NAME)
,PrimaryKeyOrder=(
	SELECT ORDINAL_POSITION
	FROM #TEMP_COLUMN_INFO_VIEW
	WHERE CONSTRAINT_TYPE='PRIMARY KEY'
	AND SCHEMA_NAME=COL.TABLE_SCHEMA
	AND TABLE_NAME=COL.TABLE_NAME
	AND COLUMN_NAME=COL.COLUMN_NAME)
,ForeignKeyName=(
	SELECT TOP 1 CONSTRAINT_NAME
	FROM #TEMP_COLUMN_INFO_VIEW
	WHERE CONSTRAINT_TYPE='FOREIGN KEY'
	AND SCHEMA_NAME=COL.TABLE_SCHEMA
	AND TABLE_NAME=COL.TABLE_NAME
	AND COLUMN_NAME=COL.COLUMN_NAME)
,ForeignKeySchemaName=(
	SELECT TOP 1 REF_SCHEMA_NAME
	FROM #TEMP_COLUMN_INFO_VIEW
	WHERE CONSTRAINT_TYPE='FOREIGN KEY'
	AND SCHEMA_NAME=COL.TABLE_SCHEMA
	AND TABLE_NAME=COL.TABLE_NAME
	AND COLUMN_NAME=COL.COLUMN_NAME)
,ForeignKeyTableName=(
	SELECT TOP 1 REF_TABLE_NAME
	FROM #TEMP_COLUMN_INFO_VIEW
	WHERE CONSTRAINT_TYPE='FOREIGN KEY'
	AND SCHEMA_NAME=COL.TABLE_SCHEMA
	AND TABLE_NAME=COL.TABLE_NAME
	AND COLUMN_NAME=COL.COLUMN_NAME)
,ForeignKeyColumnName=(
	SELECT TOP 1 REF_COLUMN_NAME
	FROM #TEMP_COLUMN_INFO_VIEW
	WHERE CONSTRAINT_TYPE='FOREIGN KEY'
	AND SCHEMA_NAME=COL.TABLE_SCHEMA
	AND TABLE_NAME=COL.TABLE_NAME
	AND COLUMN_NAME=COL.COLUMN_NAME)
,ForeignKeyColumnOrder=(
	SELECT TOP 1 ORDINAL_POSITION
	FROM #TEMP_COLUMN_INFO_VIEW
	WHERE CONSTRAINT_TYPE='FOREIGN KEY'
	AND SCHEMA_NAME=COL.TABLE_SCHEMA
	AND TABLE_NAME=COL.TABLE_NAME
	AND COLUMN_NAME=COL.COLUMN_NAME)
FROM INFORMATION_SCHEMA.COLUMNS COL
LEFT JOIN INFORMATION_SCHEMA.SCHEMATA SCH ON SCH.SCHEMA_NAME=COL.TABLE_SCHEMA
LEFT JOIN INFORMATION_SCHEMA.TABLES TBL ON TBL.TABLE_NAME=COL.TABLE_NAME
ORDER BY SchemaName,ObjectType,ObjectName,ColumnOrder;
DROP TABLE #TEMP_COLUMN_INFO_VIEW
end
GO
", SP_GetColumnObjectInfo);
            }
        }

        private string SP_GetParameterObjectInfo
        {
            get { return "sp_util_GetParameterObjectInfo"; }
        }

        private string SQL_GetParameterObjectInfo
        {
            get
            {
                return string.Format(@"
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[{0}]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[{0}]
GO

create procedure [dbo].[{0}]
as
begin
SELECT PARA.SPECIFIC_SCHEMA AS SchemaName
,SCH.SCHEMA_OWNER AS OwnerName
,CASE WHEN ROUT.ROUTINE_TYPE='PROCEDURE' THEN 1 ELSE 2 END AS ObjectType
,PARA.SPECIFIC_NAME AS ObjectName
,ISNULL(PARA.PARAMETER_NAME,'ReturnParameter') AS ParameterName
,PARA.ORDINAL_POSITION AS ParameterOrder
,PARA.DATA_TYPE AS NativeType
,PARA.CHARACTER_MAXIMUM_LENGTH AS Size
,PARA.NUMERIC_PRECISION AS Precision,PARA.NUMERIC_SCALE AS Scale
,CASE 
	WHEN PARA.IS_RESULT='YES' THEN 6
	WHEN PARA.PARAMETER_MODE='IN' THEN 1 
	WHEN PARA.PARAMETER_MODE='OUT' THEN 2 
	WHEN PARA.PARAMETER_MODE='INOUT' THEN 3 
	ELSE 1 END AS Direction
,CASE WHEN PARA.IS_RESULT='NO' THEN 0 ELSE 1 END AS IsResult
FROM INFORMATION_SCHEMA.PARAMETERS PARA
LEFT JOIN INFORMATION_SCHEMA.SCHEMATA SCH ON SCH.SCHEMA_NAME=PARA.SPECIFIC_SCHEMA
LEFT JOIN INFORMATION_SCHEMA.ROUTINES ROUT ON ROUT.ROUTINE_SCHEMA=PARA.SPECIFIC_SCHEMA AND ROUT.ROUTINE_NAME=PARA.SPECIFIC_NAME
ORDER BY SchemaName,ObjectType,ObjectName, ParameterOrder
end
GO
", SP_GetParameterObjectInfo); ;
            }
        }

        #endregion

        #region database info sqls

        private string SP_GetDbInfo
        {
            get { return "sp_util_GetDbInfo"; }
        }

        private string SQL_GetDbInfo
        {
            get
            {
                return string.Format(@"
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[{0}]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[{0}]
GO

CREATE procedure [dbo].[{0}]
AS
	
	SELECT
		ServerProperty('ProductVersion') AS ProductVersion, 
		ServerProperty('ProductLevel') AS ServicePack, 
		ServerProperty('Edition') AS ProductEdition, 
		@@VERSION AS SoftwarePlatform		

GO
", SP_GetDbInfo);
            }
        }

        private string SP_GetDbFileInfo
        {
            get { return "sp_util_GetDbFileInfo"; }
        }

        private string SQL_GetDbFileInfo
        {
            get
            {
                return string.Format(@"
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[{0}]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[{0}]
GO

CREATE procedure [dbo].[{0}]
AS
	
	SELECT 
		CASE LOWER(RIGHT(filename,3))
			WHEN 'mdf' THEN 'DATA'
			WHEN 'ldf' THEN 'LOG'
			ELSE 'UNKNOWN'
		END as FileType,
		Name,
		size*8 as Size,
        filename
	FROM sysfiles
GO
", SP_GetDbFileInfo);
            }
        }

        private string SP_GetDbBackups
        {
            get { return "sp_util_GetDbBackups"; }
        }

        private string SQL_GetDbBackups
        {
            get
            {
                return string.Format(@"
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[{0}]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[{0}]
GO
CREATE procedure [dbo].[{0}]
AS
	
EXEC ('SELECT TOP 20     
		name, 
		backup_start_date as StartDate, 
		backup_finish_date as FinishDate, 
		backup_size/1024 as size, 
		database_name, 
		CASE type
			WHEN ''D'' THEN ''Database''
			WHEN ''I'' THEN ''Differential database''
			WHEN ''L'' THEN ''Log''
			WHEN ''F'' THEN ''File or filegroup''
			WHEN ''G'' THEN ''Differential file''
			WHEN ''P'' THEN ''Partial''
			WHEN ''Q'' THEN ''Differential partial''
		END as BackupType
	FROM         
		msdb..backupset
	WHERE
		database_name = DB_NAME() 
	ORDER BY backup_start_date DESC')

GO
", SP_GetDbBackups);
            }
        }


        #endregion

        #endregion
    }
}
